package vcp.server.DAO;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import vcp.entity.Lot;
import vcp.entity.Site;
import vcp.enums.SiteEnum;
import vcp.server.DBAdapter;
import vcp.server.DAO.interfaces.IGenericDAO;
import vcp.server.utility.Constants.SiteTable;
import vcp.server.utility.Constants.TableNames;
import vcp.server.utility.SQLQueries;
/**
 * <p> site DAO class includes all relevant sql queries</p>
 * @author VCP team
 *
 */
public class SiteDAO implements IGenericDAO <Site, Void>
{
	/**
	 * updates damaged lot by ID
	 * @param ID
	 * @param damagedLots
	 * @return true if success
	 */
	public boolean updateDamagedLotsByID ( int ID, int damagedLots )
	{
		boolean success = damagedLots >= 0;
		if ( success )
		{
			String sqlQuery =
					String.format(
							SQLQueries.UPDATE__TABLENAME__SET__SINGLEVALUE__WHERE__COMPARE_
							, TableNames.SITE
							, SiteTable.DAMAGED_LOTS + "=" + String.valueOf(damagedLots)
							, SiteTable.SITE_ID + "=" + String.valueOf(ID));
			success = this.refresh(sqlQuery);
		}
		return success;
	}
	/**
	 * updates occupied lots by Id
	 * @param ID
	 * @param occupiedLots
	 * @return ture if success
	 */
	public boolean updateOccupiedLotsByID ( int ID, int occupiedLots )
	{
		boolean success = occupiedLots >= 0;
		if ( success )
		{
			String sqlQuery =
					String.format(
							SQLQueries.UPDATE__TABLENAME__SET__SINGLEVALUE__WHERE__COMPARE_
							, TableNames.SITE
							, SiteTable.OCCUPIED_LOTS + "=" + String.valueOf(occupiedLots)
							, SiteTable.SITE_ID + "=" + String.valueOf(ID));
			success = this.refresh(sqlQuery);
		}
		return success;
	}
	/**
	 * updates reserved lot by Id
	 * @param ID
	 * @param reservedLots
	 * @return true if success
	 */
	public boolean updateReservedLotsByID ( int ID, int reservedLots )
	{
		boolean success = reservedLots >= 0;
		if ( success )
		{
			String sqlQuery =
					String.format(
							SQLQueries.UPDATE__TABLENAME__SET__SINGLEVALUE__WHERE__COMPARE_
							, TableNames.SITE
							, SiteTable.RESERVED_LOTS + "=" + String.valueOf(reservedLots)
							, SiteTable.SITE_ID + "=" + String.valueOf(ID));
			success = this.refresh(sqlQuery);
		}
		return success;
	}
	/**
	 * updates single use price rate 
	 * @param ID
	 * @param newPrice
	 * @return true if success
	 */
	public boolean updateSingleUsePricePerHour ( int ID, float newPrice )
	{
		boolean success = newPrice >= 0;
		if ( success )
		{
			String sqlQuery =
					String.format(
							SQLQueries.UPDATE__TABLENAME__SET__SINGLEVALUE__WHERE__COMPARE_
							, TableNames.SITE
							, SiteTable.SINGLE_USE_HOUR + "=" + String.valueOf(newPrice)
							, SiteTable.SITE_ID + "=" + String.valueOf(ID));
			success = this.refresh(sqlQuery);
		}
		return success;
	}
	/**
	 * updates order ahead rates 
	 * @param ID
	 * @param newPrice
	 * @return true if success
	 */
	public boolean updateOrderAheadPricePerHour ( int ID, float newPrice )
	{
		boolean success = newPrice >= 0;
		if ( success )
		{
			String sqlQuery =
					String.format(
							SQLQueries.UPDATE__TABLENAME__SET__SINGLEVALUE__WHERE__COMPARE_
							, TableNames.SITE
							, SiteTable.ORDER_AHEAD_HOUR + "=" + String.valueOf(newPrice)
							, SiteTable.SITE_ID + "=" + String.valueOf(ID));
			success = this.refresh(sqlQuery);
		}
		return success;
	}
	/**
	 * updates casual subscriber with one car price
	 * @param ID
	 * @param newPrice
	 * @return true if success
	 */
	public boolean updateCasualPriceSingleCar ( int ID, float newPrice )
	{
		boolean success = newPrice >= 0;
		if ( success )
		{
			String sqlQuery =
					String.format(
							SQLQueries.UPDATE__TABLENAME__SET__SINGLEVALUE__WHERE__COMPARE_
							, TableNames.SITE
							, SiteTable.CASUAL_PRICE + "=" + String.valueOf(newPrice)
							, SiteTable.SITE_ID + "=" + String.valueOf(ID));
			success = this.refresh(sqlQuery);
		}
		return success;
	}
	/**
	 * updates full subscriber with one car rates 
	 * @param ID
	 * @param newPrice
	 * @return true if success
	 */
	public boolean updateFullPriceSingleCar ( int ID, float newPrice )
	{
		boolean success = newPrice >= 0;
		if ( success )
		{
			String sqlQuery =
					String.format(
							SQLQueries.UPDATE__TABLENAME__SET__SINGLEVALUE__WHERE__COMPARE_
							, TableNames.SITE
							, SiteTable.FULL_PRICE + "=" + String.valueOf(newPrice)
							, SiteTable.SITE_ID + "=" + String.valueOf(ID));
			success = this.refresh(sqlQuery);
		}
		return success;
	}
	/**
	 * looks for sites with available empty lots
	 * @return site
	 */
	public Site findSiteWithFreeLots ()
	{
		Site foundSite = null;
		for ( Site site : this.findAll(null) )
		{
			if ( site.getFreeLots() > 0 )
			{
				foundSite = site;
				break;
			}
		}

		return foundSite;
	}
	/**
	 * gets site lots
	 * @param site
	 */
	public void getSiteLotsOnDemand ( Site site )
	{
		LotDAO lotDAO = new LotDAO();
		List <Lot> resultList = lotDAO.findAll(site);
		site.getParkingMatrix().clear();
		for ( Lot lot : resultList )
		{
			site.getParkingMatrix().addLot(lot);
		}
	}

	/****************************************************************************************************************************
	 *** IGenericDAO methods ***
	 ****************************************************************************************************************************/

	/**
	 * Not implemented
	 */
	@Override
	public Integer findMaxID ()
	{
		return null;
	}

	@Override
	public Site create ()
	{
		return new Site();
	}

	/**
	 * not implemented
	 */
	@Override
	public Site findByEntity ( Void entity )
	{
		return null;
	}

	@Override
	public Site findByID ( int ID )
	{
		return this.findByID(String.valueOf(ID));
	}

	@Override
	public Site findByID ( String ID )
	{
		String query =
				String.format(
						SQLQueries.SELECT_ALL_FROM__SINGLETABLE__WHERE__COMPARE_
						, TableNames.SITE
						, SiteTable.SITE_ID + "=" + ID);

		Site site = null;
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			if ( set.next() )
			{
				site = this.create();

				site.setColumns(set.getInt(SiteTable.COLUMNS));
				site.setSite(SiteEnum.convert(set.getString(SiteTable.SITE_NAME)));
				site.setDamagedLots(set.getInt(SiteTable.DAMAGED_LOTS));
				site.setOccupiedLots(set.getInt(SiteTable.OCCUPIED_LOTS));
				site.setReservedLots(set.getInt(SiteTable.RESERVED_LOTS));
				site.setSingleUsePricePerHour(set.getFloat(SiteTable.SINGLE_USE_HOUR));
				site.setOrderAheadPricePerHour(set.getFloat(SiteTable.ORDER_AHEAD_HOUR));
				site.setCasualPriceSingleCar(set.getFloat(SiteTable.CASUAL_PRICE));
				site.setFullPriceSingleCar(set.getFloat(SiteTable.FULL_PRICE));
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return site;
	}

	@Override
	public List <Site> findAll ( Void info )
	{
		String query =
				String.format(
						SQLQueries.SELECT_ALL_FROM_SINGLETABLE
						, TableNames.SITE);

		List <Site> listOfSites = null;
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			listOfSites = new ArrayList <Site>();
			while ( set.next() )
			{
				Site site = this.create();

				site.setColumns(set.getInt(SiteTable.COLUMNS));
				site.setSite(SiteEnum.convert(set.getString(SiteTable.SITE_NAME)));
				site.setDamagedLots(set.getInt(SiteTable.DAMAGED_LOTS));
				site.setOccupiedLots(set.getInt(SiteTable.OCCUPIED_LOTS));
				site.setReservedLots(set.getInt(SiteTable.RESERVED_LOTS));
				site.setSingleUsePricePerHour(set.getFloat(SiteTable.SINGLE_USE_HOUR));
				site.setOrderAheadPricePerHour(set.getFloat(SiteTable.ORDER_AHEAD_HOUR));
				site.setCasualPriceSingleCar(set.getFloat(SiteTable.CASUAL_PRICE));
				site.setFullPriceSingleCar(set.getFloat(SiteTable.FULL_PRICE));

				listOfSites.add(site);
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return listOfSites;
	}

	@Override
	public boolean save ( Site entity )
	{
		boolean result = false;
		String valuesSet =
				String.format(
						"%d, %d, \"%s\", %d, %d, %d, %f, %f, %f, %f"
						, entity.getSiteID()
						, entity.getColumns()
						, entity.getSiteName()
						, entity.getDamagedLots()
						, entity.getReservedLots()
						, entity.getOccupiedLots()
						, entity.getSingleUsePricePerHour()
						, entity.getOrderAheadPricePerHour()
						, entity.getCasualPriceSingleCar()
						, entity.getFullPriceSingleCar());

		String sqlQuery =
				String.format(
						SQLQueries.INSERT_INTO__TABLENAME__VALUES__
						, TableNames.SITE
						, valuesSet);

		try
		{
			result = DBAdapter.getAdapter().save(sqlQuery);
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return result;
	}

	@Override
	public boolean refresh ( String sqlQuery )
	{
		boolean success = false;

		try
		{
			success = DBAdapter.getAdapter().refresh(sqlQuery);
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return success;
	}

	@Override
	public boolean deleteByID ( int ID )
	{
		return this.deleteByID(String.valueOf(ID));
	}

	@Override
	public boolean deleteByID ( String ID )
	{
		String sqlQuery =
				String.format(
						SQLQueries.DELETE_FROM__TABLENAME__WHERE__COMPARE_
						, TableNames.SITE
						, SiteTable.SITE_ID + "=" + ID);

		boolean success = false;
		try
		{
			success = DBAdapter.getAdapter().delete(sqlQuery);
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return success;
	}
	/**
	 * for testinf sql functionality
	 * @param args
	 */
	public static void main ( String[] args )
	{
		System.out.println("******************TESTING SITEDAO***************");
		SiteDAO testSiteDao = new SiteDAO();
		Site testSite = testSiteDao.create();

		testSite.setColumns(3);
		testSite.setSite(SiteEnum.SITE_1);
		testSite.setDamagedLots(0);
		testSite.setOccupiedLots(0);
		testSite.setReservedLots(0);

		testSiteDao.save(testSite);
		System.out.println("Site 1 was saved");
		System.out.println("*********************************************************");

		testSite = testSiteDao.create();

		testSite.setColumns(5);
		testSite.setSite(SiteEnum.SITE_2);
		testSite.setDamagedLots(0);
		testSite.setOccupiedLots(0);
		testSite.setReservedLots(0);

		testSiteDao.save(testSite);
		System.out.println("Site 2 was saved");
		System.out.println("*********************************************************");

		testSite = testSiteDao.findByID(SiteEnum.SITE_1.getSiteID());
		System.out.println("site with id:" + testSite.getSiteID() + " was found");
		System.out.println("*********************************************************");
		testSiteDao.updateDamagedLotsByID(testSite.getSiteID(), 4);
		System.out.println("site with id:" + testSite.getSiteID() + " damaged updated");
		System.out.println("*********************************************************");
		testSiteDao.updateReservedLotsByID(testSite.getSiteID(), 5);
		System.out.println("site with id:" + testSite.getSiteID() + " reserved updated");
		System.out.println("*********************************************************");
		testSiteDao.updateOccupiedLotsByID(testSite.getSiteID(), 10);
		System.out.println("site with id:" + testSite.getSiteID() + " occupied updated");
		System.out.println("*********************************************************");

		for ( Site foundSite : testSiteDao.findAll(null) )
		{
			System.out.println("site with id:" + foundSite.getSiteID() + " was found");
		}

		System.out.println("*********************************************************");
		testSiteDao.deleteByID(SiteEnum.SITE_2.getSiteID());
		System.out.println("site 2 was deleted");
	}

}
